﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Formatters.Binary;
using Gumini.JulaDB.Data;
using Gumini.JulaDB.DataTypes;
using Gumini.JulaDB.Engine;
using Gumini.JulaDB.Enums;
using Gumini.JulaDB.Expressions;
using Gumini.JulaDB.Optimizer;
using Gumini.JulaDB.Parser;
using Gumini.JulaDB.Planner;
using Gumini.JulaDB.Provider;
using Irony.Parsing;

namespace Gumini.JulaDB
{
    public class JulaDbEngine
    {
        private Database _database;
        private readonly static BinaryFormatter formatter = new BinaryFormatter();
        private readonly Irony.Parsing.Parser _parser;
        private readonly SqlGrammar _grammar;
        private readonly ExpressionBuilder _expressionBuilder;
        private readonly QueryPlanner _planner;
        private readonly QueryOptimizer _optimizer;
        private readonly string _filePath;

        public const string Version = "1.0";

        /// <param name="filePath">null for in-memory databases</param>
        private JulaDbEngine(Database database, string filePath)
        {
            if (database == null)
                throw new ArgumentNullException("database");

            _database = database;
            _grammar = new SqlGrammar();
            _parser = new Irony.Parsing.Parser(_grammar);
            _expressionBuilder = new ExpressionBuilder(_grammar);
            _planner = new QueryPlanner(_grammar, _database, _expressionBuilder);
            _optimizer = new QueryOptimizer();
            _filePath = filePath;
        }

        private static Database LoadOrCreateDatabase(string path)
        {
            using (var readStream = new FileStream(path, FileMode.OpenOrCreate))
            {
                // file is empty, we create an empty database
                if (readStream.Length == 0)
                    return new Database();
                else
                    return (Database)formatter.Deserialize(readStream);
            }
        }

        private static void SaveDatabaseToFile(Database database, string path)
        {
            using (var writeStream = new FileStream(path, FileMode.Truncate))
            {
                formatter.Serialize(writeStream, database);
            }
        }

        /// <summary>
        /// Creates an instance of an in-memory database.
        /// </summary>
        public static JulaDbEngine CreateInMemory()
        {
            var database = new Database();
            return new JulaDbEngine(database, filePath: null);
        }

        /// <summary>
        /// Opens a database from a file. If the file does not exist, a new one will be created.
        /// </summary>
        public static JulaDbEngine OpenOrCreateFile(string path)
        {
            var database = LoadOrCreateDatabase(path);
            return new JulaDbEngine(database, filePath: path);
        }

        /// <summary>
        /// Parses and executes SQL.
        /// </summary>
        /// <param name="input">The input.</param>
        /// <returns></returns>
        public IList<CommandResult> Execute(string input, bool throwOnError = false, bool useOptimizer = true)
        {
            Debug.WriteLine("Executing: " + input);
            Debug.WriteLine("Optimizer: " + (useOptimizer ? "ON" : "OFF"));

            var tree = _parser.Parse(input);

            var results = new List<CommandResult>();

            // there was a parse error
            if (tree.Status == ParseTreeStatus.Error)
            {
                if (throwOnError)
                {
                    var firstMessage = tree.ParserMessages.First();
                    throw new JulaDbException(ErrorCode.SyntaxError, new SourcePosition(firstMessage.Location), firstMessage.Message);
                }
                else
                {
                    results.Add(CommandResult.CreateFailure(
                        tree.ParserMessages.Select(m => new LogMessage(m.Message, ErrorCode.SyntaxError, new SourcePosition(m.Location)))));
                }
            }
            else // parsing was successful
            {
                Debug.Assert(tree.Root.Term == _grammar.stmtList);
                
                foreach (var statement in tree.Root.ChildNodes)
                {
                    var result = ExecuteCommand(statement, throwOnError, useOptimizer);
                    results.Add(result);
                    if (!result.IsSuccessful)
                        break;
                }
            }

            return results;
        }

        private CommandResult ExecuteCommand(ParseTreeNode node, bool throwOnError, bool useOptimizer)
        {
            try
            {
                if (node.Term == _grammar.createTableStmt)
                    return CreateTable(node);
                else if (node.Term == _grammar.dropTableStmt)
                    return DropTable(node);
                else if (node.Term == _grammar.selectStmt)
                    return Select(node, useOptimizer);
                else if (node.Term == _grammar.insertStmt)
                    return Insert(node);
                else if (node.Term == _grammar.updateStmt)
                    return Update(node);
                else if (node.Term == _grammar.deleteStmt)
                    return Delete(node);
                else
                    throw new JulaDbException(ErrorCode.UnsupportedSyntax, node.GetPosition(), "Syntax '{0}' is not supported.", node.Term);
            }
            catch (JulaDbException ex)
            {
                if (throwOnError)
                    throw;
                else
                    return CommandResult.CreateFailure(new LogMessage(ex.Message, ex.ErrorCode, ex.Position));
            }
        }

        private IEnumerable<Tuple<Row, Context<TypedValue>>> FilterRows(Table table, Expression whereExpression, bool negate)
        {
            if (whereExpression != null && whereExpression.IsAggregate)
                throw new JulaDbException(ErrorCode.AggregateMismatch, "Cannot use aggregate expresions in the WHERE condition.");

            var heading = table.Definition.Heading;
            foreach (var row in table.Rows)
            {
                // creating an evaluation context with all field names and values
                var context = row.GetEvaluationContext(heading);

                bool useRow = true;
                if (whereExpression != null)
                {
                    // evaluating the where clause if it exists
                    var result = whereExpression.Evaluate(context);
                    if (result.Type != DataType.Bool)
                        throw new JulaDbException(ErrorCode.TypeError, "The WHERE clause expression should evaluate to a boolean value.");
                    useRow = (bool)result.Value;
                }
                // check whether this row will be affected by the query
                if(negate)
                    useRow = !useRow;
                if (useRow)
                    yield return Tuple.Create(row, context);
            }
        }

        /// <summary>
        /// Creates a table based on the CREATE TABLE command.
        /// </summary>
        /// <param name="node">CREATE TABLE parse tree node.</param>
        /// <returns></returns>
        private CommandResult CreateTable(ParseTreeNode node)
        {
            var tableIdNode = node.GetSubNode(_grammar.Id);
            var fieldListNode = node.GetSubNode(_grammar.fieldDefList);

            var tableName = tableIdNode.GetName(_grammar);
            var columnDefinitions = new List<TableColumn>();
            foreach (var fieldDefNode in fieldListNode.ChildNodes)
            {
                Debug.Assert(fieldDefNode.Term == _grammar.fieldDef);
                
                var fieldIdNode = fieldDefNode.GetSubNode(_grammar.Id);
                var typeNameNode = fieldDefNode.GetSubNode(_grammar.typeName);
                var typeParamsNode = fieldDefNode.GetSubNode(_grammar.typeParamsOpt);
                var nullSpecNode = fieldDefNode.GetSubNode(_grammar.nullSpecOpt);

                string fieldName = fieldIdNode.GetName(_grammar);
                var dataType = DataType.GetType(typeNameNode.ChildNodes[0].Token.Text);
                bool isNullable = nullSpecNode.ChildNodes.Count != 2; // 2 childen mean "NOT NULL", 1 means NULL, 0 means default (NULL)
                int? length = null;
                if (typeParamsNode.ChildNodes.Count == 1)
                    length = int.Parse(typeParamsNode.ChildNodes[0].Token.Text);
                
                var columnDefinition = new TableColumn(fieldName, dataType, isNullable, length);

                columnDefinitions.Add(columnDefinition);
            }
            var definition = new TableDefinition(tableName, columnDefinitions);

            try
            {
                _database.CreateTable(definition);
            }
            catch (JulaDbException ex)
            {
                if (ex.ErrorCode == ErrorCode.DuplicateTableName)
                    ex.Position = tableIdNode.GetPosition();
                throw;
            }

            return CommandResult.CreateSuccess(
                new LogMessage(String.Format("Table '{0}' was created.", tableName)),
                rowsAffected: 0);
        }

        /// <summary>
        /// Drops a table.
        /// </summary>
        /// <param name="node">DROP TABLE parse tree node.</param>
        /// <returns></returns>
        private CommandResult DropTable(ParseTreeNode node)
        {
            var tableIdNode = node.GetSubNode(_grammar.Id);
            var tableName = tableIdNode.GetName(_grammar);

            try
            {
                _database.DropTable(tableName);
            }
            catch (JulaDbException ex)
            {
                if (ex.ErrorCode == ErrorCode.UnknownTable)
                    ex.Position = tableIdNode.GetPosition();
                throw;
            }

            return CommandResult.CreateSuccess(
                new LogMessage(String.Format("Table '{0}' was dropped.", tableName)),
                rowsAffected: 0);
        }

        /// <summary>
        /// Persorm a SELECT operation on the database.
        /// </summary>
        /// <param name="selectNode">SELECT parse tree node.</param>
        /// <returns></returns>
        private CommandResult Select(ParseTreeNode selectNode, bool useOptimizer)
        {
            // construct the query plan for the select statement
            var plan = _planner.BuildQueryPlan(selectNode);

            // try to optimize the plan
            if (useOptimizer)
                plan = _optimizer.BuildOptimizedPlan(plan);

            // return the resulting rows
            RecordSet resultSet;
            try
            {
                resultSet = plan.Execute();
            }
            catch (OutOfMemoryException)
            {
                throw new JulaDbException(ErrorCode.OutOfMemory, "Not enough memory to execute the query.");
            }

            return CommandResult.CreateSuccess(
                new LogMessage(String.Format("Select returned {0} row(s).", resultSet.Rows.Count)),
                rowsAffected: 0, resultTable: resultSet);
        }

        /// <summary>
        /// Executes an INSERT statement on the database.
        /// </summary>
        /// <param name="node">INSERT parse tree node.</param>
        private CommandResult Insert(ParseTreeNode node)
        {
            // getting the target table
            var tableName = node.GetSubNode(_grammar.Id).GetName(_grammar);
            var table = _database.GetTable(tableName);
            var heading = table.Definition.Heading;

            // getting the list of column names
            var columnIdListNode = node.GetSubNode(_grammar.idlistPar).GetSubNode(_grammar.idList);
            var columnNames = columnIdListNode.ChildNodes.Select(c => c.GetName(_grammar)).ToList();

            // get the list of values to insert
            var insertDataNode = node.GetSubNode(_grammar.insertData);
            var selectStmtNode = insertDataNode.GetSubNode(_grammar.selectStmt);
            if (selectStmtNode != null)
                throw new JulaDbException(ErrorCode.UnsupportedSyntax, selectStmtNode.GetPosition(), "SELECT in UPDATE statements is not supported.");
            var exprListNode = insertDataNode.GetSubNode(_grammar.exprList);
            var expressions = exprListNode.ChildNodes.Select(c => _expressionBuilder.CreateExpression(c)).ToList();
            if (columnNames.Count != expressions.Count)
                throw new JulaDbException(ErrorCode.NumberOfColumnsDoesNotMatch, "The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.");
            var values = expressions.Select(e => e.Evaluate(Context<TypedValue>.Empty)).ToList();
            
            // creating a new row
            var newRow = new Row(table.Definition.Heading.NumberOfColumns);
            for (int i = 0; i < columnNames.Count; i++)
            {
                int ordinal = heading.GetColumnOrdinal(columnNames[i]);
                var columnDefinition = heading[ordinal];
                var type = columnDefinition.Type;
                var value = values[i].Value;
                newRow.Assign(ordinal, type, value);
            }
            table.InsertRow(newRow);

            return CommandResult.CreateSuccess(
                new LogMessage(String.Format("Inserted 1 row to '{0}' table.", table.Definition.Name)),
                rowsAffected: 1);
        }

        /// <summary>
        /// Executes an UPDATE statement on the database.
        /// </summary>
        /// <param name="node">UPDATE parse tree node.</param>
        private CommandResult Update(ParseTreeNode node)
        {
            // getting the target table
            string tableName = node.GetSubNode(_grammar.Id).GetName(_grammar);
            var table = _database.GetTable(tableName);
            var heading = table.Definition.Heading;

            // get the WHERE expression
            var whereExpression = _planner.GetWhereExpression(node);

            // get the assignment list
            var assignList = node.GetSubNode(_grammar.assignList);
            var columnAssignments = assignList.ChildNodes.Select(c => new
            {
                ColumnName = c.ChildNodes[0].GetName(_grammar),
                ExpressionNode = c.ChildNodes[2]
            }).ToList();

            // verify that all columns exist
            foreach (var a in columnAssignments)
                if(!heading.ContainsColumn(a.ColumnName))
                    throw new JulaDbException(ErrorCode.UnknownIdentifier, "Unknown column '{0}'.", a.ColumnName);

            // get the column ordinals and prepare expressions
            var ordinalAssignments = columnAssignments.Select(a => new
            {
                Ordinal = heading.GetColumnOrdinal(a.ColumnName),
                Expr = _expressionBuilder.CreateExpression(a.ExpressionNode)
            }).ToList();

            // verify that there are no aggregate expressions
            if (ordinalAssignments.Any(a => a.Expr.IsAggregate))
                throw new JulaDbException(ErrorCode.AggregateMismatch, "Cannot use aggregate expresions in the UPDATE statement.");

            // execute the update on filtered rows
            int rowsAffected = 0;
            var filteredRows = FilterRows(table, whereExpression, false);
            foreach(var row in filteredRows)
            {
                // updating the row that matches the WHERE condition
                foreach (var a in ordinalAssignments)
                {
                    var result = a.Expr.Evaluate(row.Item2);
                    var expectedType = heading[a.Ordinal].Type;
                    row.Item1.Assign(a.Ordinal, expectedType, result.Value);
                }

                rowsAffected++;
            }

            return CommandResult.CreateSuccess(
                new LogMessage(String.Format("Updated {0} row(s).", rowsAffected)),
                rowsAffected: rowsAffected);
        }

        /// <summary>
        /// Executes a DELETE statement on the database.
        /// </summary>
        /// <param name="node">DELETE parse tree node.</param>
        private CommandResult Delete(ParseTreeNode node)
        {
            // getting the target table
            string tableName = node.GetSubNode(_grammar.Id).GetName(_grammar);
            var table = _database.GetTable(tableName);
            var heading = table.Definition.Heading;

            // get the WHERE expression
            var whereExpression = _planner.GetWhereExpression(node);
            
            // filter the rows so only the ones that would not match the WHERE condition would remain
            var remainingRows = FilterRows(table, whereExpression, true).
                Select(r => r.Item1).ToList();

            // calculate the number of affected rows
            int rowsAffected = table.NumberOfRows - remainingRows.Count;
            
            // replace the esisting rows with the filtered rows
            table.SetRows(remainingRows);
            
            return CommandResult.CreateSuccess(
                new LogMessage(String.Format("Deleted {0} row(s).", rowsAffected)),
                rowsAffected: rowsAffected);
        }

        public void Close()
        {
            if (_database == null)
                throw new InvalidOperationException("Database is not opened.");

            if (_filePath != null)
                SaveDatabaseToFile(_database, _filePath);

            _database = null;
        }
    }
}
